package com.mairuide.phone.callanalysis.service;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.mairuide._frame.base.BaseRepository;
import com.mairuide._frame.base.BaseServiceProxy;
import com.mairuide._frame.utils.CalendarUtils;
import com.mairuide._frame.utils.ListUtils;
import com.mairuide.bill.utils.DateUtils;
import com.mairuide.entity.PageResult;
import com.mairuide.entity.constant.ConstantPool;
import com.mairuide.httpclient.BaseSite;
import com.mairuide.phone.PhoneRecord;
import com.mairuide.phone.callanalysis.CallanalysisVo;
import com.mairuide.phone.callanalysis.dao.CallAnalysisRepository;
import com.mairuide.phone.callanalysis.entity.PhoneMapDetail;
import com.mairuide.phone.callanalysis.entity.PhoneSearchDto;

/**
 * Created by 敲代码的卡卡罗特
 * on 2018/12/17 12:19.
 */
@Service
public class CallAnalysisService extends BaseServiceProxy<PhoneRecord> {

    @Autowired
    private CallAnalysisRepository callAnalysisRepository;

    @PersistenceContext
    private EntityManager entityManager;
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Resource
    private BaseSite baseSite;

    @Override
    protected EntityManager getEntityManager() {
        return entityManager;
    }

    @Override
    protected BaseRepository<PhoneRecord> getBaseRepository() {
        return callAnalysisRepository;
    }

    /**
     * 通话详情分析
     * @param whereMap
     * @param page
     * @param size
     * @return
     */
    public PageResult detail(CallanalysisVo whereMap, int page, int size) {
        StringBuffer sb=new StringBuffer();
        sb.append(" SELECT *  ");
        sb.append(" from TB_PHONE_RECORDING WHERE 1=1 ");
        if (StringUtils.isNotBlank(whereMap.getCASEMANAGE_ID())){ // 案件id
            sb.append(" and  CASEMANAGE_ID = '" + whereMap.getCASEMANAGE_ID()+"'");
        }
        if (whereMap.getCALL_TYPE()!=null){ // 呼叫类型
            sb.append(" and CALL_TYPE = " +whereMap.getCALL_TYPE() );
        }
        if (StringUtils.isNotBlank(whereMap.getSERVICE_USER_INFORMATION())){ // 对方姓名
            sb.append(" and SERVICE_USER_INFORMATION = '" + whereMap.getSERVICE_USER_INFORMATION()+"'");
        }
        if (StringUtils.isNotBlank(whereMap.getPARTY_NUMBER())){ // 对方号码
            sb.append(" and PARTY_NUMBER in (" + whereMap.getPARTY_NUMBER()+") ");
        }
        if (StringUtils.isNotBlank(whereMap.getPARTY_LOCATION())){ // 对方号码归属地
            sb.append(" and PARTY_LOCATION like '%" + whereMap.getPARTY_LOCATION()+"%'");
        }
        if (StringUtils.isNotBlank(whereMap.getBegin_time())){ // 开始时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') >= '" + whereMap.getBegin_time()+"'");
        }
        if (StringUtils.isNotBlank(whereMap.getEnd_time())){ // 结束时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') <= '" + whereMap.getEnd_time()+"'");
        }
        String service_number = whereMap.getSERVICE_NUMBER();
        if (StringUtils.isNotBlank(service_number)){ // 电话号码集合
            sb.append(" and SERVICE_NUMBER in (" + service_number +")");
        }
        sb.append(" ORDER BY STARTING_TIME DESC");
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
        Query query = this.entityManager.createNativeQuery(sb.toString());
        //将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        query.setFirstResult((page-1)*size);
        query.setMaxResults(size);
        //获取查询结果集
        List<Map<String, Object>> list = query.getResultList();

        PageResult pageResult = new PageResult(Long.valueOf(maps.size()),list,null);
        return pageResult;
    }
    public PageResult detail_easyui(PhoneSearchDto dto) {
        StringBuffer sb=new StringBuffer();
        sb.append(" SELECT *  ");
        sb.append(" from TB_PHONE_RECORDING WHERE 1=1 ");
        if (StringUtils.isNotBlank(dto.getCaseId())){ // 案件id
            sb.append(" and  CASEMANAGE_ID = '" + dto.getCaseId()+"'");
        }
        if (StringUtils.isNotBlank(String.valueOf(dto.getCallType()))){ // 呼叫类型
            sb.append(" and CALL_TYPE = " +dto.getCallType()) ;
        }
        if (StringUtils.isNotBlank(dto.getServiceUserInformation())){ // 对方姓名
            sb.append(" and SERVICE_USER_INFORMATION = '" + dto.getServiceUserInformation()+"'");
        }
        if (StringUtils.isNotBlank(dto.getPartyNumber())){ // 对方号码
            sb.append(" and PARTY_NUMBER in (" + dto.getPartyNumber()+") ");
        }
        if (StringUtils.isNotBlank(dto.getPartyLocation())){ // 对方号码归属地
            sb.append(" and PARTY_LOCATION like '%" + dto.getPartyLocation()+"%'");
        }
        if (StringUtils.isNotBlank(dto.getBeginTime())){ // 开始时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') >= '" + dto.getBeginTime()+"'");
        }
        if (StringUtils.isNotBlank(dto.getEndTime())){ // 结束时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') <= '" + dto.getEndTime()+"'");
        }
        if (StringUtils.isNotBlank(dto.getActiveCity())){//主叫城市
            sb.append(" and SERVICE_LOCATION like '%" + dto.getActiveCity()+"%'");
        }
        if (StringUtils.isNotBlank(dto.getPassiveCity())){//被叫城市
            sb.append(" and PARTY_LOCATION like '%" + dto.getPassiveCity()+"%'");
        }
        String service_number = dto.getServiceNumber();
        if (StringUtils.isNotBlank(service_number)){ // 电话号码集合
           /* String[] split = StringUtils.split(service_number, ",");
            for (int i = 0; i < split.length; i++) {
                split[i]="'"+split[i]+"'";
            }
            service_number = StringUtils.join(split, ",");*/
            sb.append(" and SERVICE_NUMBER in (" + service_number +")");
        }else{
            sb.append(" and 1=0 ");
        }
        if (StringUtils.isNotBlank(dto.getSort())) {
            sb.append(" order by " + dto.getSort() + " " + dto.getOrder());
        }else{
            sb.append(" ORDER BY STARTING_TIME DESC");
        }
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
        Query query = this.entityManager.createNativeQuery(sb.toString());
        //将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        query.setFirstResult((dto.getPage()-1)*dto.getRows());
        query.setMaxResults(dto.getRows());
        //获取查询结果集
        List<Map<String, Object>> list = query.getResultList();
        getSite(list);//获得基站位置
        PageResult pageResult = new PageResult(Long.valueOf(maps.size()),list,null);
        return pageResult;
    }
    /**
     * 通话详情地图分析
     * @param whereMap
     * @param page
     * @param size
     * @return
     */
    public PageResult detail_map(CallanalysisVo whereMap ) {
        StringBuffer sb=new StringBuffer();
        sb.append(" SELECT RECORD_ID,nvl(CID,'0') CID ,nvl(LAC,'0') LAC,SERVICE_NUMBER,nvl(LNGB,'0') LNGB,nvl(LATB,'0') LATB,nvl(ADDRESS ,'0') ADDRESS,to_char(STARTING_TIME,'yyyy-mm-dd hh24:mi:ss') STARTING_TIME,PARTY_NUMBER,CALL_TYPE,TALK_TIME ");
        sb.append(" from TB_PHONE_RECORDING WHERE 1=1 ");
        if (StringUtils.isNotBlank(whereMap.getCASEMANAGE_ID())){ // 案件id
            sb.append(" and  CASEMANAGE_ID = '" + whereMap.getCASEMANAGE_ID()+"'");
        }
        if (whereMap.getCALL_TYPE()!=null){ // 呼叫类型
            sb.append(" and CALL_TYPE = " +String.valueOf(whereMap.getCALL_TYPE())+"");
        }
        if (StringUtils.isNotBlank(whereMap.getSERVICE_USER_INFORMATION())){ // 对方姓名
            sb.append(" and SERVICE_USER_INFORMATION = '" + whereMap.getSERVICE_USER_INFORMATION()+"'");
        }
        if (StringUtils.isNotBlank(whereMap.getPARTY_NUMBER())){ // 对方号码
            sb.append(" and PARTY_NUMBER in (" + whereMap.getPARTY_NUMBER()+") ");
        }
        if (StringUtils.isNotBlank(whereMap.getPARTY_LOCATION())){ // 对方号码归属地
            sb.append(" and PARTY_LOCATION like '%" + whereMap.getPARTY_LOCATION()+"%'");
        }
        if (StringUtils.isNotBlank(whereMap.getBegin_time())){ // 开始时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') >= '" + whereMap.getBegin_time()+"'");
        }
        if (StringUtils.isNotBlank(whereMap.getEnd_time())){ // 结束时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd hh24:mi:ss') <= '" + whereMap.getEnd_time()+" 23:59:59'");
        }
        if (StringUtils.isNotBlank(whereMap.getSERVICE_NUMBER())){ // 电话号码集合
            sb.append(" and SERVICE_NUMBER in (" + whereMap.getSERVICE_NUMBER()+")");
        }else{
            sb.append(" and 1=0 ");
        }
        sb.append(" ORDER BY STARTING_TIME DESC");
        List<PhoneMapDetail> maps = jdbcTemplate.query(sb.toString(),new BeanPropertyRowMapper<PhoneMapDetail>(PhoneMapDetail.class));
        //封装地图数据
        List arrayList_hava = baseSite.getMap(maps);
        HashMap<String, Object> result = new HashMap<>();
        result.put("data",arrayList_hava);
        
        List<PhoneMapDetail> resultList = maps;
        for(PhoneMapDetail pd : (List<PhoneMapDetail>) arrayList_hava) {
        	for(PhoneMapDetail pd2 :resultList) {
        		if(!pd2.getCID().equals("0") && !pd2.getLAC().equals("0")) {
        			if(pd.getCID().equals(pd2.getCID()) && pd.getLAC().equals(pd2.getLAC())) {
        				pd2.setADDRESS(pd.getADDRESS());
        			}
        		}
        	}
        }
        result.put("data2",resultList);
        PageResult pageResult = new PageResult(Long.valueOf(maps.size()),null,result);
        return pageResult;
    }

    /**
     * 通话频率分析
     * @param whereMap
     * @param page
     * @param size
     * @return
     */
    public PageResult frequency(Map<String,String> whereMap, int page, int size) {
        StringBuffer sb=new StringBuffer();
        sb.append(" select * FROM (SELECT SERVICE_NUMBER,PARTY_NUMBER,PARTY_LOCATION,sum( TALK_TIME ) AS sum_TALK_TIME,round( avg( TALK_TIME ), 2 ) AS avg_TALK_TIME,count( 1 ) AS count ,min(STARTING_TIME) AS beigin_time,max(STARTING_TIME) AS end_time  ");
        sb.append(" from TB_PHONE_RECORDING WHERE 1=1 ");
        if (StringUtils.isNotBlank(whereMap.get("CASEMANAGE_ID"))){ // 案件id
            sb.append(" and  CASEMANAGE_ID = '" + whereMap.get("CASEMANAGE_ID")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("CALL_TYPE"))){ // 呼叫类型
            sb.append(" and CALL_TYPE = '" + whereMap.get("CALL_TYPE")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("SERVICE_USER_INFORMATION"))){ // 对方姓名
            sb.append(" and SERVICE_USER_INFORMATION = '" + whereMap.get("SERVICE_USER_INFORMATION")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("PARTY_NUMBER"))){ // 对方号码
            sb.append(" and PARTY_NUMBER in (" + whereMap.get("PARTY_NUMBER")+")");
        }
        if (StringUtils.isNotBlank(whereMap.get("PARTY_LOCATION"))){ // 对方号码归属地
            sb.append(" and PARTY_LOCATION like '%" + whereMap.get("PARTY_LOCATION")+"%'");
        }
        if (StringUtils.isNotBlank(whereMap.get("begin_time"))){ // 开始时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') >= '" + whereMap.get("begin_time")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("end_time"))){ // 结束时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') <= '" + whereMap.get("end_time")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("SERVICE_NUMBER"))){ // 电话号码集合
            sb.append(" and SERVICE_NUMBER in (" + whereMap.get("SERVICE_NUMBER")+")");
        } else {
            sb.append(" and 1=0");
        }
        sb.append(" GROUP BY SERVICE_NUMBER, PARTY_NUMBER, PARTY_LOCATION ) p where 1=1 ");
        if(StringUtils.isNotBlank(whereMap.get("FREQUENCY_COUNT"))){ //次数
            sb.append(" and p.count >= " + whereMap.get("FREQUENCY_COUNT"));
        }
        if(StringUtils.isNotBlank(whereMap.get("COUNT_ORDER"))){ //次数排序
            sb.append(" ORDER BY count " + whereMap.get("COUNT_ORDER"));
        }
        if(StringUtils.isNotBlank(whereMap.get("SUM_TALK_TIME_ORDER"))){ //总时长
            sb.append(" ORDER BY SUM_TALK_TIME " + whereMap.get("SUM_TALK_TIME_ORDER"));
        }
        if(StringUtils.isNotBlank(whereMap.get("AVG_TALK_TIME_ORDER"))){ //总时长
            sb.append(" ORDER BY AVG_TALK_TIME " + whereMap.get("AVG_TALK_TIME_ORDER"));
        }
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
        Query query = this.entityManager.createNativeQuery(sb.toString());
        //将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        query.setFirstResult((page-1)*size);
        query.setMaxResults(size);
        //获取查询结果集
        List<Map<String, Object>> list = query.getResultList();
        PageResult pageResult = new PageResult(Long.valueOf(maps.size()),list,null);
        return pageResult;
    }
    public PageResult frequency_easyui(PhoneSearchDto dto) {
        StringBuffer sb=new StringBuffer();
        sb.append(" select * FROM (SELECT SERVICE_NUMBER,PARTY_NUMBER,PARTY_LOCATION,sum( TALK_TIME ) AS sum_TALK_TIME,round( avg( TALK_TIME ), 2 ) AS avg_TALK_TIME,count( 1 ) AS count ,min(STARTING_TIME) AS beigin_time,max(STARTING_TIME) AS end_time  ");
        sb.append(" from TB_PHONE_RECORDING WHERE 1=1 ");
        if (StringUtils.isNotBlank(dto.getCaseId())){ // 案件id
            sb.append(" and  CASEMANAGE_ID = '" +dto.getCaseId()+"'");
        }
        if (StringUtils.isNotBlank(dto.getCallType())){ // 呼叫类型
            sb.append(" and CALL_TYPE = '" + dto.getCallType()+"'");
        }
        if (StringUtils.isNotBlank(dto.getServiceUserInformation())){ // 对方姓名
            sb.append(" and SERVICE_USER_INFORMATION = '" + dto.getServiceUserInformation()+"'");
        }
        if (StringUtils.isNotBlank(dto.getPartyNumber())){ // 对方号码
            sb.append(" and PARTY_NUMBER in (" + dto.getPartyNumber()+")");
        }
        if (StringUtils.isNotBlank(dto.getPartyLocation())){ // 对方号码归属地
            sb.append(" and PARTY_LOCATION like '%" + dto.getPartyLocation()+"%'");
        }
        if (StringUtils.isNotBlank(dto.getBeginTime())){ // 开始时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') >= '" + dto.getBeginTime()+"'");
        }
        if (StringUtils.isNotBlank(dto.getEndTime())){ // 结束时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') <= '" + dto.getEndTime()+"'");
        }
        if (StringUtils.isNotBlank(dto.getServiceNumber())){ // 电话号码集合
            sb.append(" and SERVICE_NUMBER in (" + dto.getServiceNumber()+")");
        } else {
            sb.append(" and 1=0");
        }
        sb.append(" GROUP BY SERVICE_NUMBER, PARTY_NUMBER, PARTY_LOCATION ) p where 1=1 ");
        if(StringUtils.isNotBlank(dto.getFrequencyCount())){ //次数
            sb.append(" and p.count >= " + dto.getFrequencyCount());
        }
        if (StringUtils.isNotBlank(dto.getSort())) {
            sb.append(" order by " + dto.getSort() + " " + dto.getOrder());
        }
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
        Query query = this.entityManager.createNativeQuery(sb.toString());
        //将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        query.setFirstResult((dto.getPage()-1)*dto.getRows());
        query.setMaxResults(dto.getRows());
        //获取查询结果集
        List<Map<String, Object>> list = query.getResultList();
        PageResult pageResult = new PageResult(Long.valueOf(maps.size()),list,null);
        return pageResult;
    }



    /**
     * 通话频率地图分析
     * @param whereMap
     * @param page
     * @param size
     * @return
     */
    public PageResult frequencyMap(Map<String,String> whereMap) {
        return frequencyDetail2(whereMap, 1, Integer.MAX_VALUE);
    }

    /**
     * 通话频率折线分析
     * @param whereMap
     * @param page
     * @param size
     * @return
     */
    public List frequencyBrokenLine(Map<String,String> whereMap) {
        StringBuffer sb=new StringBuffer();
        sb.append(" SELECT h.HOUR, nvl( p.COUNT, 0 ) count FROM  ");
        if("天".equals(whereMap.get("time_type"))){
            sb.append(" ( SELECT to_char( LEVEL - 1, 'fm00' ) hour FROM dual CONNECT BY LEVEL <= 24 ) h ");
        }else if ("月".equals(whereMap.get("time_type"))){
            sb.append(" ( SELECT to_char( to_date( '"+whereMap.get("Time")+"', 'yyyy-MM-dd' ) + LEVEL - 1, 'yyyy-MM-dd' ) hour FROM dual CONNECT BY LEVEL <= to_char( last_day( to_date( '"+whereMap.get("Time")+"', 'yyyy-MM-dd' )), 'dd' ) ) h ");
        }else if ("年".equals(whereMap.get("time_type"))){
            sb.append(" ( SELECT to_char( add_months( to_date('2018-01','yyyy-mm'), LEVEL - 1 )  , 'yyyy-MM' ) hour  FROM dual CONNECT BY LEVEL <= 12 ) h ");
        }

        if("天".equals(whereMap.get("time_type"))){
            sb.append(" LEFT JOIN ( SELECT count( 1 ) count, to_char( STARTING_TIME, 'hh24' ) h1 FROM  TB_PHONE_RECORDING  WHERE 1 = 1  ");
        }else if ("月".equals(whereMap.get("time_type"))){
            sb.append(" LEFT JOIN ( SELECT count( 1 ) count, to_char( STARTING_TIME, 'yyyy-mm-dd' ) h1 FROM  TB_PHONE_RECORDING  WHERE 1 = 1  ");
        }else if ("年".equals(whereMap.get("time_type"))){
            sb.append(" LEFT JOIN ( SELECT count( 1 ) count, to_char( STARTING_TIME, 'yyyy-mm' ) h1 FROM  TB_PHONE_RECORDING  WHERE 1 = 1  ");
        }
        if (StringUtils.isNotBlank(whereMap.get("CASEMANAGE_ID"))){ // 案件id
            sb.append(" and  CASEMANAGE_ID = '" + whereMap.get("CASEMANAGE_ID")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("PARTY_NUMBER"))){ // 对方号码
            sb.append(" and PARTY_NUMBER = '" + whereMap.get("PARTY_NUMBER")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("SERVICE_NUMBER"))){ // 主叫号码
            sb.append(" and SERVICE_NUMBER = '" + whereMap.get("SERVICE_NUMBER")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("PARTY_LOCATION"))){ // 对方号码归属地
            sb.append(" and PARTY_LOCATION = '" + whereMap.get("PARTY_LOCATION")+"'");
        }
        if("天".equals(whereMap.get("time_type"))){
            if (StringUtils.isNotBlank(whereMap.get("Time"))){ // 时间
                sb.append(" and to_char(STARTING_TIME, 'yyyy-mm-dd') = '" + whereMap.get("Time")+"'");
            }
            sb.append(" GROUP BY to_char(STARTING_TIME,'hh24') ) p ON p.h1 = h.hour ORDER BY h.hour");
        }else if("月".equals(whereMap.get("time_type"))){
            if (StringUtils.isNotBlank(whereMap.get("Time"))){ // 时间
                sb.append(" and to_char(STARTING_TIME, 'yyyy-mm') = '" + DateUtils.formatTimeString(whereMap.get("Time"),"yyyy-MM")+"'");
            }
            sb.append(" GROUP BY to_char(STARTING_TIME,'yyyy-mm-dd') ) p ON p.h1 = h.hour ORDER BY h.hour");
        }else if("年".equals(whereMap.get("time_type"))){
            if (StringUtils.isNotBlank(whereMap.get("Time"))){ // 时间
                sb.append(" and to_char(STARTING_TIME, 'yyyy') = '" + DateUtils.formatTimeString(whereMap.get("Time"),"yyyy")+"'");
            }
            sb.append(" GROUP BY to_char(STARTING_TIME,'yyyy-mm') ) p ON p.h1 = h.hour ORDER BY h.hour");
        }
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
        return maps;
    }



    /**
     * 通话频率详情分析
     * @param whereMap
     * @param page
     * @param size
     * @return
     */
    public PageResult frequencyDetail(Map<String,String> whereMap,Integer page,Integer size) {
        StringBuffer sb=new StringBuffer();
        sb.append(" SELECT * FROM  TB_PHONE_RECORDING WHERE 1=1");
        if (StringUtils.isNotBlank(whereMap.get("CASEMANAGE_ID"))){ // 案件id
            sb.append(" and  CASEMANAGE_ID = '" + whereMap.get("CASEMANAGE_ID")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("DATE_HOUR"))){ // 时间段
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd hh24') = '" + whereMap.get("DATE_HOUR")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("PARTY_NUMBER"))){ // 对方号码
            sb.append(" and PARTY_NUMBER in (" + whereMap.get("PARTY_NUMBER")+") ");
        }else if (StringUtils.isNotBlank(whereMap.get("PARTY_NUMBER_LIST"))){ //对方全部手机号集合
            sb.append(" and PARTY_NUMBER in (" + whereMap.get("PARTY_NUMBER_LIST")+") ");
        }
        if (StringUtils.isNotBlank(whereMap.get("SERVICE_NUMBER"))){ // 主叫号码
            sb.append(" and SERVICE_NUMBER in (" + whereMap.get("SERVICE_NUMBER")+" ) ");
        }
        if (StringUtils.isNotBlank(whereMap.get("PARTY_LOCATION"))){ // 对方号码归属地
            sb.append(" and PARTY_LOCATION = '" + whereMap.get("PARTY_LOCATION")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("begin_time"))){ // 开始时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') >= '" + whereMap.get("begin_time")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("end_time"))){ // 结束时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') <= '" + whereMap.get("end_time")+"'");
        }
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
        Query query = this.entityManager.createNativeQuery(sb.toString());
        //将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        //设置分页
        query.setFirstResult((page-1)*size);
        query.setMaxResults(size);
        //获取查询结果集
        List<Map<String, Object>> list = query.getResultList();
        PageResult pageResult = new PageResult(Long.valueOf(maps.size()),list,maps);
        return pageResult;
    }
    
    /**
     * 通话频率详情分析
     * @param whereMap
     * @param page
     * @param size
     * @return
     */
    public PageResult frequencyDetail2(Map<String,String> whereMap,Integer page,Integer size) {
    	StringBuffer sb=new StringBuffer();
    	sb.append(" SELECT RECORD_ID,nvl(CID,'0') CID ,nvl(LAC,'0') LAC,SERVICE_NUMBER,nvl(LNGB,'0') LNGB,nvl(LATB,'0') LATB,nvl(ADDRESS ,'0') ADDRESS,to_char(STARTING_TIME,'yyyy-mm-dd hh24:mi:ss') STARTING_TIME,PARTY_NUMBER,CALL_TYPE,TALK_TIME  FROM  TB_PHONE_RECORDING WHERE 1=1");
    	if (StringUtils.isNotBlank(whereMap.get("CASEMANAGE_ID"))){ // 案件id
    		sb.append(" and  CASEMANAGE_ID = '" + whereMap.get("CASEMANAGE_ID")+"'");
    	}
    	if (StringUtils.isNotBlank(whereMap.get("DATE_HOUR"))){ // 时间段
    		sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd hh24') = '" + whereMap.get("DATE_HOUR")+"'");
    	}
    	if (StringUtils.isNotBlank(whereMap.get("PARTY_NUMBER"))){ // 对方号码
    		sb.append(" and PARTY_NUMBER in (" + whereMap.get("PARTY_NUMBER")+") ");
    	}else if (StringUtils.isNotBlank(whereMap.get("PARTY_NUMBER_LIST"))){ //对方全部手机号集合
    		sb.append(" and PARTY_NUMBER in (" + whereMap.get("PARTY_NUMBER_LIST")+") ");
    	}
    	if (StringUtils.isNotBlank(whereMap.get("SERVICE_NUMBER"))){ // 主叫号码
    		sb.append(" and SERVICE_NUMBER in (" + whereMap.get("SERVICE_NUMBER")+" ) ");
    	}
    	if (StringUtils.isNotBlank(whereMap.get("PARTY_LOCATION"))){ // 对方号码归属地
    		sb.append(" and PARTY_LOCATION = '" + whereMap.get("PARTY_LOCATION")+"'");
    	}
    	if (StringUtils.isNotBlank(whereMap.get("begin_time"))){ // 开始时间
    		sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') >= '" + whereMap.get("begin_time")+"'");
    	}
    	if (StringUtils.isNotBlank(whereMap.get("end_time"))){ // 结束时间
    		sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') <= '" + whereMap.get("end_time")+"'");
    	}
    	sb.append(" ORDER BY STARTING_TIME DESC");
        List<PhoneMapDetail> maps = jdbcTemplate.query(sb.toString(),new BeanPropertyRowMapper<PhoneMapDetail>(PhoneMapDetail.class));
        //封装地图数据
        List arrayList_hava = baseSite.getMap(maps);
        HashMap<String, Object> result = new HashMap<>();
        result.put("data",arrayList_hava);
        List<PhoneMapDetail> resultList = maps;
        for(PhoneMapDetail pd : (List<PhoneMapDetail>) arrayList_hava) {
        	for(PhoneMapDetail pd2 :resultList) {
        		if(!pd2.getCID().equals("0") && !pd2.getLAC().equals("0")) {
        			if(pd.getCID().equals(pd2.getCID()) && pd.getLAC().equals(pd2.getLAC())) {
        				pd2.setADDRESS(pd.getADDRESS());
        			}
        		}
        	}
        }
        result.put("data2",resultList);
        PageResult pageResult = new PageResult(Long.valueOf(maps.size()),null,result);
    	return pageResult;
    }
    public PageResult phoneDetail_easyui(CallanalysisVo vo) {
        StringBuffer sb=new StringBuffer();
        sb.append(" SELECT * FROM  TB_PHONE_RECORDING WHERE 1=1");
        if (StringUtils.isNotBlank(vo.getCASEMANAGE_ID())){ // 案件id
            sb.append(" and  CASEMANAGE_ID = '" + vo.getCASEMANAGE_ID()+"'");
        }
        if (StringUtils.isNotBlank(vo.getPARTY_NUMBER())){ // 对方号码
            sb.append(" and PARTY_NUMBER = '" + vo.getPARTY_NUMBER()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getSERVICE_NUMBER())){ // 主叫号码
            sb.append(" and SERVICE_NUMBER ='" + vo.getSERVICE_NUMBER()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getBegin_time())){ // 开始时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') >= '" + vo.getBegin_time()+"'");
        }
        if (StringUtils.isNotBlank(vo.getEnd_time())){ // 结束时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') <= '" + vo.getEnd_time()+"'");
        }
        if (StringUtils.isNotBlank(vo.getDATE_HOUR())){ // 时间段
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd hh24') = '" + vo.getDATE_HOUR()+"'");
        }
        if (StringUtils.isNotBlank(vo.getSort())) {
            sb.append(" order by " + vo.getSort() + " " + vo.getOrder());
        }
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
        Query query = this.entityManager.createNativeQuery(sb.toString());
        //将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        //设置分页
        query.setFirstResult((vo.getPage()-1)*vo.getRows());
        query.setMaxResults(vo.getRows());
        //获取查询结果集
        List<Map<String, Object>> list = query.getResultList();
        getSite(list);//获得基站位置
        PageResult pageResult = new PageResult(Long.valueOf(maps.size()),list);
        return pageResult;
    }

    /**
     * 通话频率规律分析
     * @param whereMap
     * @param page
     * @param size
     * @return
     */
    public HashMap<String, Object> frequencyLaw(Map<String,String> whereMap) {
        StringBuffer sb=new StringBuffer();
        sb.append(" SELECT to_char( STARTING_TIME, 'yyyy-MM-dd HH24' ) DAY,COUNT( 1 )  count FROM TB_PHONE_RECORDING  WHERE 1=1");
        if (StringUtils.isNotBlank(whereMap.get("CASEMANAGE_ID"))){ // 案件id
            sb.append(" and  CASEMANAGE_ID = '" + whereMap.get("CASEMANAGE_ID")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("PARTY_NUMBER"))){ // 对方号码
            sb.append(" and PARTY_NUMBER in (" + whereMap.get("PARTY_NUMBER")+") ");
        }
        if (StringUtils.isNotBlank(whereMap.get("SERVICE_USER_INFORMATION"))){ // 对方姓名
            sb.append(" and SERVICE_USER_INFORMATION = '" + whereMap.get("SERVICE_USER_INFORMATION")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("SERVICE_NUMBER"))){ // 主叫号码
            sb.append(" and SERVICE_NUMBER in (" + whereMap.get("SERVICE_NUMBER")+") ");
        }
        if (StringUtils.isNotBlank(whereMap.get("PARTY_LOCATION"))){ // 对方号码归属地
            sb.append(" and PARTY_LOCATION = '" + whereMap.get("PARTY_LOCATION")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("begin_time"))){ // 开始时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') >= '" + whereMap.get("begin_time")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("end_time"))){ // 结束时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') <= '" + whereMap.get("end_time")+"'");
        }
        sb.append(" GROUP BY to_char( STARTING_TIME, 'yyyy-MM-dd HH24' ) ORDER BY to_char( STARTING_TIME, 'yyyy-MM-dd HH24' )  ");
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
        List<String> dateTimeList=maps.stream().map(m->(String)m.get("day")).collect(Collectors.toList());
        ArrayList<Map<String, Object>> list = new ArrayList<>();
        if(maps!=null&&maps.size()==0){
            HashMap<String, Object> map = new HashMap<>();
            map.put("mapData",null);
            map.put("data",null);
            return map;
        }
        //得到第一天和最后一天的时间
        Map<String, Object> first_day = maps.get(0);
        String day1 = (String)first_day.get("DAY");
        String s1 = day1.split(" ")[0];
        Map<String, Object> last_day = maps.get(maps.size() - 1);
        String day2 = (String)last_day.get("DAY");
        String s2 = day2.split(" ")[0];
        //得到日期段集合
        List<String> dateSlot = CalendarUtils.getDateSlot(s1, s2);
        //得到日期时间段集合
        List<String> dateAndHourList = ConstantPool.getDateAndHourList(dateSlot);
        ArrayList<Map<String, Object>> maps1 = new ArrayList<>();
        for(int i=0;i<dateAndHourList.size();i++){
            String time = dateAndHourList.get(i);
            if(dateTimeList.contains(time)){
                int i1 = dateTimeList.indexOf(time);
                Map<String, Object> stringObjectMap = maps.get(i1);
                maps1.add(stringObjectMap);
            }else{
                HashMap<String, Object> stringObjectHashMap = new HashMap<>();
                stringObjectHashMap.put("day",time);
                stringObjectHashMap.put("count",0);
                maps1.add(stringObjectHashMap);
            }
        }


        List<List<?>> lists = ListUtils.splitList(maps1, 24);
        for(int i=0;i<lists.size();i++){
            HashMap<String, Object> stringObjectHashMap = new HashMap<>();
            stringObjectHashMap.put("时间段",dateSlot.get(i));
            List<Map<String, Object>> objects = (List<Map<String, Object>>) lists.get(i);
            int sum=0;
            for (int j=0;j<objects.size();j++){
                //stringObjectHashMap.put("a"+ConstantPool.HOURSLIST.get(j),objects.get(j).get("count"));
                sum+= Integer.parseInt(objects.get(j).get("count").toString());
            }
            stringObjectHashMap.put("timeVal",objects);
            stringObjectHashMap.put("合计",sum);
            list.add(stringObjectHashMap);
        }

        HashMap<String, Object> map = new HashMap<>();
        map.put("data",list);
        return map;
    }
    public List<Map<String,Object>> frequencyLaw_easyui(PhoneSearchDto dto ) {
        StringBuffer sb=new StringBuffer();
        sb.append(" SELECT to_char( STARTING_TIME, 'yyyy-MM-dd HH24' ) DAY,COUNT( 1 )  count FROM TB_PHONE_RECORDING  WHERE 1=1");
        if (StringUtils.isNotBlank(dto.getCaseId())){ // 案件id
            sb.append(" and  CASEMANAGE_ID = '" + dto.getCaseId()+"'");
        }
        if (StringUtils.isNotBlank(dto.getPartyNumber())){ // 对方号码
            sb.append(" and PARTY_NUMBER in (" + dto.getPartyNumber()+") ");
        }
        if (StringUtils.isNotBlank(dto.getServiceUserInformation())){ // 对方姓名
            sb.append(" and SERVICE_USER_INFORMATION = '" + dto.getServiceUserInformation()+"'");
        }
        if (StringUtils.isNotBlank(dto.getServiceNumber())){ // 主叫号码
            sb.append(" and SERVICE_NUMBER in (" + dto.getServiceNumber()+") ");
        }else{
            sb.append(" and 1=0 ");
        }
        if (StringUtils.isNotBlank(dto.getPartyLocation())){ // 对方号码归属地
            sb.append(" and PARTY_LOCATION = '" + dto.getPartyLocation()+"'");
        }
        if (StringUtils.isNotBlank(dto.getBeginTime())){ // 开始时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') >= '" + dto.getBeginTime()+"'");
        }
        if (StringUtils.isNotBlank(dto.getEndTime())){ // 结束时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') <= '" + dto.getEndTime()+"'");
        }
        sb.append(" GROUP BY to_char( STARTING_TIME, 'yyyy-MM-dd HH24' ) ORDER BY to_char( STARTING_TIME, 'yyyy-MM-dd HH24' )  ");
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());
        if (maps.size()==0){
            List l= new ArrayList();
            return l;
        }
        List<String> dateTimeList=maps.stream().map(m->(String)m.get("day")).collect(Collectors.toList());
        ArrayList<Map<String, Object>> list = new ArrayList<>();
        //得到第一天和最后一天的时间
        Map<String, Object> first_day = maps.get(0);
        String day1 = (String)first_day.get("DAY");
        String s1 = day1.split(" ")[0];
        Map<String, Object> last_day = maps.get(maps.size() - 1);
        String day2 = (String)last_day.get("DAY");
        String s2 = day2.split(" ")[0];
        //得到日期段集合
        List<String> dateSlot = CalendarUtils.getDateSlot(s1, s2);
        //得到日期时间段集合
        List<String> dateAndHourList = ConstantPool.getDateAndHourList(dateSlot);
        ArrayList<Map<String, Object>> maps1 = new ArrayList<>();
        for(int i=0;i<dateAndHourList.size();i++){
            String time = dateAndHourList.get(i);
            if(dateTimeList.contains(time)){
                int i1 = dateTimeList.indexOf(time);
                Map<String, Object> stringObjectMap = maps.get(i1);
                maps1.add(stringObjectMap);
            }else{
                HashMap<String, Object> stringObjectHashMap = new HashMap<>();
                stringObjectHashMap.put("day",time);
                stringObjectHashMap.put("count",0);
                maps1.add(stringObjectHashMap);
            }
        }
        List<List<?>> lists = ListUtils.splitList(maps1, 24);
        for(int i=0;i<lists.size();i++){
            HashMap<String, Object> stringObjectHashMap = new HashMap<>();
            stringObjectHashMap.put("time",dateSlot.get(i));
            List<Map<String, Object>> objects = (List<Map<String, Object>>) lists.get(i);
            int sum=0;
            for (int j=0;j<objects.size();j++){
                Map<String, Object> map = objects.get(j);
                String day = map.get("day").toString();
                day=day.split(" ")[1];
                sum+= Integer.parseInt(map.get("count").toString());
                stringObjectHashMap.put("t"+day,map);
            }
            //stringObjectHashMap.put("timeVal",objects);
            stringObjectHashMap.put("total",sum);
            list.add(stringObjectHashMap);
        }
        return list;
    }

    /**
     * 通话频率规律分析
     * @param whereMap
     * @param page
     * @param size
     * @return
     */
    public HashMap<String, Object> frequencyLawMap(Map<String,String> whereMap) {
        //得到地图数据
        Map callInfoByIds=new HashMap();
        try {
            List<Map<String,Object>> map1 = (List<Map<String,Object>>)frequencyDetail(whereMap, 1, Integer.MAX_VALUE).getAllRows();
            callInfoByIds = baseSite.getCallInfoByIds(BaseSite.getBaseSiteIds(map1));
            List<String> baseSiteList = BaseSite.getBaseSiteList(map1);
            List<Map<String,Object>> data = (List<Map<String,Object>>)callInfoByIds.get("data");
            ArrayList<Map<String,Object>> list1 = new ArrayList<>();
            if(data!=null){
                for (Map<String,Object> m:data){
                    list1.add(map1.get(baseSiteList.indexOf((String) m.get("id"))));
                }
            }
            callInfoByIds.put("data2",list1);
        } catch (Exception e) {
            e.printStackTrace();
        }

        HashMap<String, Object> map = new HashMap<>();
        map.put("mapData",callInfoByIds);
        return map;
    }

    /**
     * 通话公共联系人分析
     * @param whereMap
     * @return
     */
    public HashMap<String, Object> commonContactsPeople(Map<String,String> whereMap) {
        //查询出匹配到的被叫人。
        StringBuffer sb=new StringBuffer();
        sb.append(" SELECT * FROM ");
        sb.append(" (SELECT PARTY_NUMBER , PARTY_USER_INFORMATION , count( 1 ) count FROM ( ");
        sb.append(getSQLCondition(whereMap));
        sb.append(" ) GROUP BY PARTY_NUMBER, PARTY_USER_INFORMATION  ) p WHERE 1=1 ");
        if (StringUtils.isNotBlank(whereMap.get("CONTACTS_PEOPLE_COUNT"))){ // 通话人数
            sb.append(" and p.count  >=  " + whereMap.get("CONTACTS_PEOPLE_COUNT")+"");
        }else{
            sb.append(" and p.count  >  1 ");
        }
        List<Map<String, Object>> map1 = jdbcTemplate.queryForList(sb.toString());

        //查出通话详情的记录
        List<Map<String, Object>> map2 = jdbcTemplate.queryForList(getSQLCondition(whereMap).toString());
        List<String> collect = map2.stream().map(m -> {
            String service_number = (String) m.get("SERVICE_NUMBER");
            String PARTY_NUMBER = (String) m.get("PARTY_NUMBER");
            return service_number + "-" + PARTY_NUMBER;
        }).collect(Collectors.toList());
        //勾选的主叫人员
        String service_number = whereMap.get("SERVICE_NUMBER");
        String[] service_number_arr = service_number.split(",");
        /*for (int i = 0; i < service_number_arr.length; i++) {
            service_number_arr[i]=service_number_arr[i].substring(1,service_number_arr[i].length()-1);
        }*/
        //最后返回的值
        List<Map<String, Object>> list =new ArrayList<Map<String, Object>>();
        for(int i=0;i<map1.size();i++){
            HashMap<String, Object> stringObjectHashMap = new HashMap<>();
            Map<String, Object> o1 = map1.get(i);
            stringObjectHashMap.put("ContactsPeople",o1); //联系人
            ArrayList<Map<String, Object>> list2 = new ArrayList<>();
            for(int j=0;j<service_number_arr.length;j++){
               String p = service_number_arr[j]+"-"+ (String) o1.get("PARTY_NUMBER");
                int i1 = collect.indexOf(p);
                if(i1!=-1){
                    Map<String, Object> stringObjectMap = map2.get(collect.indexOf(p));
                    list2.add(stringObjectMap);
                }else{
                    list2.add(null);
                }
            }
            stringObjectHashMap.put("body",list2);
            list.add(stringObjectHashMap);
        }

        //计算列统计
        HashMap<String, Object> map4 = new HashMap<>();
        HashMap<String, Object> obj = new HashMap<>();
        obj.put("PARTY_NUMBER","汇总累计");
        obj.put("PARTY_USER_INFORMATION","汇总累计");
        map4.put("ContactsPeople",obj);

        ArrayList<Map<String, Object>> list2 = new ArrayList<>();
        for (int i=0;i<service_number_arr.length;i++){
            Integer count_s=0;
            Integer SUM_TALK_TIME_s=0;
            ArrayList<Timestamp> timestamp = new ArrayList<>();
            for (int j=0;j<list.size();j++){
                List<Map<String, Object>> l= (List<Map<String, Object>>)(list.get(j).get("body"));
                Map<String, Object> value = l.get(i);
                if(value!=null){
                    count_s += Integer.parseInt((String.valueOf(value.get("COUNT") ))) ;//次数
                    if (!"null".equals(value.get("SUM_TALK_TIME")))SUM_TALK_TIME_s +=Integer.parseInt((String.valueOf(value.get("SUM_TALK_TIME") )))  ;//秒数
                    Timestamp MIN_STARTING_TIME =value.get("MIN_STARTING_TIME")==null||value.get("MIN_STARTING_TIME").equals("")?null:(Timestamp)value.get("MIN_STARTING_TIME");//最小时间
                    Timestamp MAX_STARTING_TIME =value.get("MAN_STARTING_TIME")==null||value.get("MAN_STARTING_TIME").equals("")?null:(Timestamp)value.get("MAN_STARTING_TIME");//最大时间
                    if (MIN_STARTING_TIME!=null){
                        timestamp.add(MIN_STARTING_TIME);
                    }
                    if (MAX_STARTING_TIME!=null){
                        timestamp.add(MAX_STARTING_TIME);
                    }
                }
            }
            Collections.sort(timestamp);
            Map<String, Object> m = new HashMap<>();
            m.put("COUNT",count_s);
            m.put("SERVICE_NUMBER",service_number_arr[i]);
            m.put("SUM_TALK_TIME",SUM_TALK_TIME_s);
            m.put("MIN_STARTING_TIME",timestamp.size()==0?"":timestamp.get(0));
            m.put("MAX_STARTING_TIME",timestamp.size()==0?"":timestamp.get(timestamp.size()-1));
            list2.add(m);
        }
        map4.put("body",list2);
        list.add(map4);


        HashMap<String, Object> map = new HashMap<>();
        map.put("body",list);

        //统计行总计
        for(int k=0;k<list.size();k++){
            List<Map<String, Object>> l= (List<Map<String, Object>>)(list.get(k).get("body"));
            ArrayList<Timestamp> timestamp = new ArrayList<>();
            String PARTY_NUMBER="";
            Integer count_s=0;
            Integer SUM_TALK_TIME_s=0;
            for(int i=0;i<l.size();i++){
                Map<String, Object> value=l.get(i);
                if(value!=null){
                    PARTY_NUMBER=String.valueOf(value.get("PARTY_NUMBER"));
                    count_s += Integer.parseInt((String.valueOf(value.get("COUNT") ))) ;//次数
                    SUM_TALK_TIME_s +=Integer.parseInt((String.valueOf(value.get("SUM_TALK_TIME") )))  ;//秒数
                    Timestamp MIN_STARTING_TIME =value.get("MIN_STARTING_TIME")==null||value.get("MIN_STARTING_TIME").equals("")?null:(Timestamp)value.get("MIN_STARTING_TIME");//最小时间
                    Timestamp MAX_STARTING_TIME =value.get("MAN_STARTING_TIME")==null||value.get("MAX_STARTING_TIME").equals("")?null:(Timestamp)value.get("MAX_STARTING_TIME");//最小时间
                    if (MIN_STARTING_TIME!=null){
                        timestamp.add(MIN_STARTING_TIME);
                    }
                    if (MAX_STARTING_TIME!=null){
                        timestamp.add(MAX_STARTING_TIME);
                    }
                }
            }
            Collections.sort(timestamp);
            Map<String, Object> m = new HashMap<>();
            m.put("PARTY_NUMBER",PARTY_NUMBER);
            m.put("count_s",count_s);
            m.put("SUM_TALK_TIME_s",SUM_TALK_TIME_s);
            m.put("minTime",timestamp.size()==0?"":timestamp.get(0));
            m.put("maxTime",timestamp.size()==0?"":timestamp.get(timestamp.size()-1));
            list.get(k).put("total",m);
        }


        //得到地图数据
        /*Map callInfoByIds=new HashMap();
        try {
            List<Map<String,Object>> map3 = (List<Map<String,Object>>)frequencyDetail(whereMap, 1, 10).getAllRows();
            callInfoByIds = baseSite.getCallInfoByIds(BaseSite.getBaseSiteIds(map3));
            if(String.valueOf(callInfoByIds.get("code")).equals("20000")){
                List<String> baseSiteList = BaseSite.getBaseSiteList(map3);
                List<Map<String,Object>> data = (List<Map<String,Object>>)callInfoByIds.get("data");
                ArrayList<Map<String,Object>> list1 = new ArrayList<>();
                if(data!=null){
                    for (Map<String,Object> m:data){
                        list1.add(map3.get(baseSiteList.indexOf((String) m.get("id"))));
                    }
                }
                callInfoByIds.put("data2",list1);
            }else{
                callInfoByIds.put("data2",null);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        map.put("mapData",callInfoByIds);*/
        return map;
    }
    public List<Map<String, Object>> commonContactsPeople_easyui(PhoneSearchDto dto ) {
        //查询出匹配到的被叫人。
        StringBuffer sb=new StringBuffer();
        sb.append(" SELECT * FROM ");
        sb.append(" (SELECT PARTY_NUMBER , PARTY_USER_INFORMATION , count( 1 ) count FROM ( ");
        sb.append(getSQLCondition_easyui(dto));
        sb.append(" ) GROUP BY PARTY_NUMBER, PARTY_USER_INFORMATION  ) p WHERE 1=1 ");
        if (StringUtils.isNotBlank(dto.getContactsPeopleCount())){ // 通话人数CONTACTS_PEOPLE_COUNT
            sb.append(" and p.count  >=  " + dto.getContactsPeopleCount()+"");
        }else{
            sb.append(" and p.count  >  1 ");
        }
        List<Map<String, Object>> map1 = jdbcTemplate.queryForList(sb.toString());

        //查出通话详情的记录
        List<Map<String, Object>> map2 = jdbcTemplate.queryForList(getSQLCondition_easyui(dto));
        List<String> collect = map2.stream().map(m -> {
            String service_number = (String) m.get("SERVICE_NUMBER");
            String PARTY_NUMBER = (String) m.get("PARTY_NUMBER");
            return service_number + "-" + PARTY_NUMBER;
        }).collect(Collectors.toList());
        //勾选的主叫人员
        String service_number =dto.getServiceNumber();
        String[] service_number_arr = service_number.split(",");

        //最后返回的值
        List<Map<String, Object>> list =new ArrayList<Map<String, Object>>();
        for(int i=0;i<map1.size();i++){
            HashMap<String, Object> stringObjectHashMap = new HashMap<>();
            Map<String, Object> o1 = map1.get(i);
            stringObjectHashMap.put("ContactsPeople",o1); //联系人
            ArrayList<Map<String, Object>> list2 = new ArrayList<>();
            for(int j=0;j<service_number_arr.length;j++){
                String p = service_number_arr[j]+"-"+ (String) o1.get("PARTY_NUMBER");
                int i1 = collect.indexOf(p);
                if(i1!=-1){
                    Map<String, Object> stringObjectMap = map2.get(collect.indexOf(p));
                    list2.add(stringObjectMap);
                }else{
                    list2.add(null);
                }
            }
            stringObjectHashMap.put("body",list2);
            list.add(stringObjectHashMap);
        }

        //计算列统计
        HashMap<String, Object> map4 = new HashMap<>();
        HashMap<String, Object> obj = new HashMap<>();
        obj.put("PARTY_NUMBER","汇总累计");
        obj.put("PARTY_USER_INFORMATION","汇总累计");
        map4.put("ContactsPeople",obj);

        ArrayList<Map<String, Object>> list2 = new ArrayList<>();
        for (int i=0;i<service_number_arr.length;i++){
            Integer count_s=0;
            Integer SUM_TALK_TIME_s=0;
            ArrayList<Timestamp> timestamp = new ArrayList<>();
            for (int j=0;j<list.size();j++){
                List<Map<String, Object>> l= (List<Map<String, Object>>)(list.get(j).get("body"));
                Map<String, Object> value = l.get(i);
                if(value!=null){
                    count_s += Integer.parseInt((String.valueOf(value.get("COUNT") ))) ;//次数
                    if (!"null".equals(value.get("SUM_TALK_TIME")))SUM_TALK_TIME_s +=Integer.parseInt((String.valueOf(value.get("SUM_TALK_TIME") )))  ;//秒数
                    Timestamp MIN_STARTING_TIME =value.get("MIN_STARTING_TIME")==null||value.get("MIN_STARTING_TIME").equals("")?null:(Timestamp)value.get("MIN_STARTING_TIME");//最小时间
                    Timestamp MAX_STARTING_TIME =value.get("MAX_STARTING_TIME")==null||value.get("MAX_STARTING_TIME").equals("")?null:(Timestamp)value.get("MAX_STARTING_TIME");//最大时间
                    if (MIN_STARTING_TIME!=null){
                        timestamp.add(MIN_STARTING_TIME);
                    }
                    if (MAX_STARTING_TIME!=null){
                        timestamp.add(MAX_STARTING_TIME);
                    }
                }
            }
            Collections.sort(timestamp);
            Map<String, Object> m = new HashMap<>();
            m.put("COUNT",count_s);
            m.put("SERVICE_NUMBER",service_number_arr[i]);
            m.put("SUM_TALK_TIME",SUM_TALK_TIME_s);
            m.put("MIN_STARTING_TIME",timestamp.size()==0?"":timestamp.get(0));
            m.put("MAX_STARTING_TIME",timestamp.size()==0?"":timestamp.get(timestamp.size()-1));
            list2.add(m);
        }
        map4.put("body",list2);
        list.add(map4);


        HashMap<String, Object> map = new HashMap<>();
        map.put("body",list);

        //统计行总计
        for(int k=0;k<list.size();k++){
            List<Map<String, Object>> l= (List<Map<String, Object>>)(list.get(k).get("body"));
            ArrayList<Timestamp> timestamp = new ArrayList<>();
            String PARTY_NUMBER="";
            Integer count_s=0;
            Integer SUM_TALK_TIME_s=0;
            for(int i=0;i<l.size();i++){
                Map<String, Object> value=l.get(i);
                if(value!=null){
                    PARTY_NUMBER=String.valueOf(value.get("PARTY_NUMBER"));
                    count_s += Integer.parseInt((String.valueOf(value.get("COUNT") ))) ;//次数
                    SUM_TALK_TIME_s +=Integer.parseInt((String.valueOf(value.get("SUM_TALK_TIME") )))  ;//秒数
                    Timestamp MIN_STARTING_TIME =value.get("MIN_STARTING_TIME")==null||value.get("MIN_STARTING_TIME").equals("")?null:(Timestamp)value.get("MIN_STARTING_TIME");//最小时间
                    Timestamp MAX_STARTING_TIME =value.get("MAX_STARTING_TIME")==null||value.get("MAX_STARTING_TIME").equals("")?null:(Timestamp)value.get("MAX_STARTING_TIME");//最小时间
                    if (MIN_STARTING_TIME!=null){
                        timestamp.add(MIN_STARTING_TIME);
                    }
                    if (MAX_STARTING_TIME!=null){
                        timestamp.add(MAX_STARTING_TIME);
                    }
                }
            }
            Collections.sort(timestamp);
            Map<String, Object> m = new HashMap<>();
            m.put("PARTY_NUMBER",PARTY_NUMBER);
            m.put("count_s",count_s);
            m.put("SUM_TALK_TIME_s",SUM_TALK_TIME_s);
            m.put("minTime",timestamp.size()==0?"":timestamp.get(0));
            m.put("maxTime",timestamp.size()==0?"":timestamp.get(timestamp.size()-1));
            list.get(k).put("total",m);
        }

        //修改返回的数据结构
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> result = list.get(i);
            List<Map<String,Object>> body = (List<Map<String,Object>>)result.get("body");
            for (int j = 0; j < body.size(); j++) {
                Map<String, Object> map3 = body.get(j);
                if (map3!=null){
                    Object serviceNumber = map3.get("SERVICE_NUMBER");
                    if(service_number!=null){
                        String service_number1 = serviceNumber.toString();
                        result.put(service_number1,map3);
                    }
                }
            }
        }
        return list;
    }

    /**
     *
     * @author 刘中华
     * @date 2018/12/22 14:30
     * @param [whereMap]
     * @return java.lang.String
     * @Description 因为两个查询语句用到了同一个sql部分的内容 抽离出来
     */
    public String getSQLCondition(Map<String,String> whereMap){
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT * FROM ( SELECT SERVICE_NUMBER,SERVICE_USER_INFORMATION,PARTY_USER_INFORMATION,PARTY_NUMBER ,count( 1 ) count ,sum(TALK_TIME) SUM_TALK_TIME, min(STARTING_TIME) min_STARTING_TIME,max(STARTING_TIME) max_STARTING_TIME ");
        sb.append(" FROM TB_PHONE_RECORDING  WHERE 1=1 ");
        if (StringUtils.isNotBlank(whereMap.get("CASEMANAGE_ID"))){ // 案件id
            sb.append(" and  CASEMANAGE_ID = '" + whereMap.get("CASEMANAGE_ID")+"'");
        }
        String service_number = whereMap.get("SERVICE_NUMBER");
        if (StringUtils.isNotBlank(service_number)){ // 主叫手机号
            if (!service_number.contains("'")){
                String[] split = StringUtils.split(service_number, ",");
                for (int i = 0; i < split.length; i++) {
                    split[i]="'"+split[i]+"'";
                }
                service_number=StringUtils.join(split,",");
            }
            sb.append(" and  SERVICE_NUMBER in ( " + service_number +" ) ");
        }
        if (StringUtils.isNotBlank(whereMap.get("PARTY_USER_INFORMATION"))){ // 对方姓名
            sb.append(" and  PARTY_USER_INFORMATION = '" + whereMap.get("PARTY_USER_INFORMATION")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("PARTY_NUMBER"))){ // 对方号码
            sb.append(" and  PARTY_NUMBER in ( " + whereMap.get("PARTY_NUMBER")+" ) ");
        }
        if (StringUtils.isNotBlank(whereMap.get("begin_time"))){ // 开始时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') >= '" + whereMap.get("begin_time")+"'");
        }
        if (StringUtils.isNotBlank(whereMap.get("end_time"))){ // 结束时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') <= '" + whereMap.get("end_time")+"'");
        }
        sb.append(" GROUP BY SERVICE_NUMBER, SERVICE_USER_INFORMATION,PARTY_NUMBER,PARTY_USER_INFORMATION ) ");
        sb.append(" WHERE 1=1 ");
        if (StringUtils.isNotBlank(whereMap.get("CONTACTS_COUNT"))){ // 通话次数
            sb.append(" and count  >=  " + whereMap.get("CONTACTS_COUNT")+"");
        }else{
            sb.append(" and count  >=  1 ");
        }
        return sb.toString();
    }
    public String getSQLCondition_easyui(PhoneSearchDto dto ){
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT * FROM ( SELECT SERVICE_NUMBER,SERVICE_USER_INFORMATION,PARTY_USER_INFORMATION,PARTY_NUMBER ,count( 1 ) count ,sum(TALK_TIME) SUM_TALK_TIME, min(STARTING_TIME) min_STARTING_TIME,max(STARTING_TIME) max_STARTING_TIME ");
        sb.append(" FROM TB_PHONE_RECORDING  WHERE 1=1 ");
        if (StringUtils.isNotBlank(dto.getCaseId())){ // 案件id
            sb.append(" and  CASEMANAGE_ID = '" + dto.getCaseId()+"'");
        }
        String service_number = dto.getServiceNumber();
        if (StringUtils.isNotBlank(service_number)){ // 主叫手机号
            if (!service_number.contains("'")){
                String[] split = StringUtils.split(service_number, ",");
                for (int i = 0; i < split.length; i++) {
                    split[i]="'"+split[i]+"'";
                }
                service_number=StringUtils.join(split,",");
            }
            sb.append(" and  SERVICE_NUMBER in ( " + service_number +" ) ");
        }
        if (StringUtils.isNotBlank(dto.getPartyUserInformation())){ // 对方姓名
            sb.append(" and  PARTY_USER_INFORMATION = '" + dto.getPartyUserInformation()+"'");
        }
        if (StringUtils.isNotBlank(dto.getPartyNumber())){ // 对方号码
            sb.append(" and  PARTY_NUMBER in ( " + dto.getPartyNumber()+" ) ");
        }
        if (StringUtils.isNotBlank(dto.getBeginTime())){ // 开始时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd') >= '" + dto.getBeginTime()+"'");
        }
        if (StringUtils.isNotBlank(dto.getEndTime())){ // 结束时间
            sb.append(" and  to_char(STARTING_TIME,'yyyy-mm-dd hh24:mi:ss') <= '" + dto.getEndTime()+" 23:59:59'");
        }
        sb.append(" GROUP BY SERVICE_NUMBER, SERVICE_USER_INFORMATION,PARTY_NUMBER,PARTY_USER_INFORMATION ) ");
        sb.append(" WHERE 1=1 ");
        if (StringUtils.isNotBlank(dto.getContactsCount())){ // 通话次数
            sb.append(" and count  >=  " + dto.getContactsCount()+"");
        }else{
            sb.append(" and count  >=  1 ");
        }
        return sb.toString();
    }


    /**
     * 动态条件构建
     * @param searchMap
     * @return
     */
    private Specification<PhoneRecord> createSpecification(Map searchMap){

        return new Specification<PhoneRecord>() {

            @Override
            public Predicate toPredicate(Root<PhoneRecord> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                List<Predicate> predicateList = new ArrayList<Predicate>();
                // 呼叫类型
                if (searchMap.get("callType")!=null && !"".equals(searchMap.get("callType"))) {
                    predicateList.add(cb.equal(root.get("callType").as(String.class), (String)searchMap.get("callType")));
                }
                // 对方号码
                if (searchMap.get("partyNumber")!=null && !"".equals(searchMap.get("partyNumber"))) {
                    predicateList.add(cb.equal(root.get("partyNumber").as(String.class), (String)searchMap.get("partyNumber")));
                }
                // 对方号码归属地
                if (searchMap.get("partyLocation")!=null && !"".equals(searchMap.get("partyLocation"))) {
                    predicateList.add(cb.like(root.get("partyLocation").as(String.class), "%"+(String)searchMap.get("partyLocation")+"%"));
                }
                // 开始时间
                if (searchMap.get("beginTime")!=null && !"".equals(searchMap.get("beginTime"))) {
                    predicateList.add(cb.greaterThanOrEqualTo(root.get("beginTime").as(String.class), (String)searchMap.get("beginTime")));
                }
                // 结束时间
                if (searchMap.get("endTime")!=null && !"".equals(searchMap.get("endTime"))) {
                    predicateList.add(cb.lessThanOrEqualTo(root.get("endTime").as(String.class), (String)searchMap.get("endTime")));
                }

                // 电话号码集合
                if (searchMap.get("numberList")!=null && !"".equals(searchMap.get("numberList"))) {
                    Expression<String> exp = root.<String>get("cardNumber");
                    predicateList.add(exp.in(searchMap.get("numberList")));
                }
                return cb.and( predicateList.toArray(new Predicate[predicateList.size()]));

            }
        };

    }
    
    private void getSite(List<Map<String, Object>> list) {
    	 Set<PhoneMapDetail> phoneMapDetailList = new HashSet<>();
         if(list!=null && list!=null) {
 			for(Map<String, Object> pr:list) {
 				if(		StringUtils.isNotEmpty(pr.get("SERVICE_NUMBER")==null?"":pr.get("SERVICE_NUMBER").toString()) 
 						&& StringUtils.isEmpty(pr.get("ADDRESS")==null?"":pr.get("ADDRESS").toString()) 
 						&& StringUtils.isNotEmpty(pr.get("CID")==null?"":pr.get("CID").toString()) 
 						&& StringUtils.isNotEmpty(pr.get("LAC")==null?"":pr.get("LAC").toString())) {
 					PhoneMapDetail detail = new PhoneMapDetail();
 					detail.setCID(StringUtils.isEmpty(pr.get("CID").toString())?"0":pr.get("CID").toString());
 					detail.setLAC(StringUtils.isEmpty(pr.get("LAC").toString())?"0":pr.get("LAC").toString());
 					detail.setLATB(StringUtils.isEmpty(pr.get("LATB")==null?"":pr.get("LATB").toString())?"0":pr.get("LATB").toString());
 					detail.setLNGB(StringUtils.isEmpty(pr.get("LNGB")==null?"":pr.get("LNGB").toString())?"0":pr.get("LNGB").toString());
 					detail.setADDRESS(StringUtils.isEmpty(pr.get("ADDRESS")==null?"":pr.get("ADDRESS").toString())?"0":pr.get("ADDRESS").toString());
 					detail.setSERVICE_NUMBER(pr.get("SERVICE_NUMBER").toString());
 					phoneMapDetailList.add(detail);
 				}
 			}
 		}
         List<PhoneMapDetail> arrayList = new ArrayList<>(phoneMapDetailList);
 		if(arrayList.size()>0) {
 			List arrayList_hava = baseSite.getMap(arrayList);
 			if(arrayList_hava!=null && arrayList_hava.size()>0) {
 				for(PhoneMapDetail detail:(List<PhoneMapDetail>)arrayList_hava) {
 					for(Map<String, Object> pr:list) {
 						if(StringUtils.isNotEmpty(pr.get("SERVICE_NUMBER")==null?"":pr.get("SERVICE_NUMBER").toString()) 
 								&& StringUtils.isEmpty(pr.get("ADDRESS")==null?"":pr.get("ADDRESS").toString()) 
 								&& StringUtils.isNotEmpty(pr.get("CID")==null?"":pr.get("CID").toString()) 
 								&& StringUtils.isNotEmpty(pr.get("LAC")==null?"":pr.get("LAC").toString())) {
 							if((pr.get("CID").toString()).equals(detail.getCID()) && (pr.get("LAC").toString()).equals(detail.getLAC())) {
 								pr.put("ADDRESS", detail.getADDRESS());
 							}
 						}
 					}
 				}
 			}
 		}
    }
}
